package servlet;

import java.sql.*;

/**
 * Created by v on 17-5-4.
 *
 * 用于连接LoginSys的数据库
 */
public class Sql {
    private static String url="jdbc:mysql://localhost:3306/loginSys";
    private static Sql sql=null;

    private Connection connection=null;
    private Statement statement=null;

    //私有方法防止创建其它的sql
    private Sql(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection(url,"v","008748");
            statement=connection.createStatement();
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

    //获取sql对象
    public static Sql getSql() {
        if(sql==null){
            sql=new Sql();
        }
        return sql;
    }

    //写入一条login的记录
    public void write(String username,String password,String sex,
                      String question,String answer,String email){
        String sql=String.format("INSERT INTO user(username,password," +
                        "sex,question,answer,email) " +
                "VALUES('%s',md5('%s'),'%s','%s',md5('%s'),'%s');",
                username,password,sex,question,answer,email);

        try {
            statement.executeUpdate(sql);
        }catch (SQLException ex){
            System.out.println("语句插入失败");
            ex.printStackTrace();
        }
    }

    //返回是否通过该用户的登录
    public boolean release(String username,String pwd){
        String sql="SELECT username FROM user WHERE " +
                "username='"+username+"'" +
                "&&password=md5('"+pwd+"');";
        try {
            ResultSet set=statement.executeQuery(sql);
            if(!set.next()){
                set.close();
                return false;
            }else {
                set.close();
                return true;
            }
        }catch (SQLException ex){
            return false;
        }
    }


    public String findPassword(String username,String question,String answer){
        String sql="SELECT password FROM user " +
                "WHERE username='"+username+"'" +
                "&&question='"+question+"'" +
                "&&answer=md5('"+answer+"');";

        try {
            ResultSet set=statement.executeQuery(sql);
            if(!set.next()){
                System.out.println("a");
                return null;
            }else {
                System.out.println("c");
                return set.getString(1);
            }
        }catch (SQLException ex){
            ex.printStackTrace();
            return null;
        }
    }
}
